{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "binding-delta",
   "metadata": {
    "papermill": {
     "duration": 0.020333,
     "end_time": "2022-01-10T17:24:10.286674",
     "exception": false,
     "start_time": "2022-01-10T17:24:10.266341",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "# alchemy_sql_query"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bb97e294-9399-4d96-a95c-8ad7e29a2872",
   "metadata": {
    "papermill": {
     "duration": 0.02484,
     "end_time": "2022-01-10T17:24:10.332454",
     "exception": false,
     "start_time": "2022-01-10T17:24:10.307614",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "Execute arbitrary SQL queries against a remote RDBMS supported by python's SQLAlchemy library"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "satellite-vegetation",
   "metadata": {
    "papermill": {
     "duration": 9.842965,
     "end_time": "2022-01-10T17:24:20.193078",
     "exception": false,
     "start_time": "2022-01-10T17:24:10.350113",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "!pip install sqlalchemy==1.4.29 pandas==1.3.5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "beginning-wisdom",
   "metadata": {
    "papermill": {
     "duration": 0.807389,
     "end_time": "2022-01-10T17:24:21.027448",
     "exception": false,
     "start_time": "2022-01-10T17:24:20.220059",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "import glob\n",
    "import logging\n",
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy.orm import sessionmaker\n",
    "from sqlalchemy.sql import text\n",
    "import pandas as pd\n",
    "import os\n",
    "import shutil\n",
    "import sys\n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "abstract-cambridge",
   "metadata": {
    "papermill": {
     "duration": 0.030419,
     "end_time": "2022-01-10T17:24:21.081588",
     "exception": false,
     "start_time": "2022-01-10T17:24:21.051169",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# type of database server (sqlalchemy dialect), e.g. postgresql\n",
    "db_type = os.environ.get('db_type', 'postgresql')\n",
    "\n",
    "# hostname of database server\n",
    "host = os.environ.get('host')\n",
    "\n",
    "# database name\n",
    "database = os.environ.get('database')\n",
    "\n",
    "# db user\n",
    "user = os.environ.get('user')\n",
    "\n",
    "# db password\n",
    "password = os.environ.get('password')\n",
    "\n",
    "# db port\n",
    "port = int(os.environ.get('port', 5432))\n",
    "\n",
    "# SQL statement\n",
    "sql = os.environ.get('sql')\n",
    "\n",
    "# file containing SQL statements\n",
    "sql_file = os.environ.get('sql_file')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "55f9b39f-2c8c-4ab5-b4f5-513357bf20ba",
   "metadata": {
    "papermill": {
     "duration": 0.030513,
     "end_time": "2022-01-10T17:24:21.135225",
     "exception": false,
     "start_time": "2022-01-10T17:24:21.104712",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "parameters = list(\n",
    "    map(lambda s: re.sub('$', '\"', s),\n",
    "        map(\n",
    "            lambda s: s.replace('=', '=\"'),\n",
    "            filter(\n",
    "                lambda s: s.find('=') > -1 and bool(re.match(r'[A-Za-z0-9_]*=[.\\/A-Za-z0-9]*', s)),\n",
    "                sys.argv\n",
    "            )\n",
    "    )))\n",
    "\n",
    "\n",
    "for parameter in parameters:\n",
    "    logging.warning('Parameter: ' + parameter)\n",
    "    exec(parameter)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "36b4bad3-4ad4-4316-9233-820239a86688",
   "metadata": {
    "papermill": {
     "duration": 0.031479,
     "end_time": "2022-01-10T17:24:21.190810",
     "exception": false,
     "start_time": "2022-01-10T17:24:21.159331",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "logging.warning(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f7081e1f-3066-4a94-914c-cc59bfb4021b",
   "metadata": {
    "papermill": {
     "duration": 0.951576,
     "end_time": "2022-01-10T17:24:22.167337",
     "exception": true,
     "start_time": "2022-01-10T17:24:21.215761",
     "status": "failed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "engine = create_engine(f'{db_type}://{user}:{password}@{host}:{port}/{database}')\n",
    "with engine.connect() as con:\n",
    "    if sql_file is not None:\n",
    "        with open(sql_file, 'r') as file:\n",
    "            sql = file.read().replace('\\n', '')\n",
    "    con.execute(text(sql))"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.6"
  },
  "papermill": {
   "default_parameters": {},
   "duration": 13.375772,
   "end_time": "2022-01-10T17:24:22.500076",
   "environment_variables": {},
   "exception": true,
   "input_path": "/home/romeokienzler/gitco/claimed/component-library/transform/alchemy-sql-query.ipynb",
   "output_path": "/home/romeokienzler/gitco/claimed/component-library/transform/alchemy-sql-query.ipynb",
   "parameters": {},
   "start_time": "2022-01-10T17:24:09.124304",
   "version": "2.3.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
